using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
//using CoOperativeManagementSystem

namespace CoOperativeManagementSystem
{
    public partial class frmMGMT : Form
    {
        DataGridViewButtonColumn editButton;
        DataGridViewButtonColumn deleteButton;
        public OleDbConnection database;
        public dataBaseCtrl dbCtrl;
        public frmMGMT()
        {
            dbCtrl = new dataBaseCtrl();

            InitializeComponent();
        }

        private void frmMGMT_Load(object sender, EventArgs e)
        {

            dbCtrl.openConnection();

        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            Close();
        }



        public void ShowMemberData(System.Windows.Forms.DataGridView datagrid, string sqlQueryString)
        {
            OleDbCommand SQLQuery = new OleDbCommand();
            DataTable data = null;
            datagrid.DataSource = null;
            datagrid.DataSource = null;
            SQLQuery.Connection = null;
            OleDbDataAdapter dataAdapter = null;
            datagrid.Columns.Clear(); // <-- clear columns

            //---------------------------------
            SQLQuery.CommandText = sqlQueryString;
            SQLQuery.Connection = dbCtrl.database;
            data = new DataTable();
            dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
            datagrid.DataSource = data;
            datagrid.AllowUserToAddRows = false; // remove the null line
            datagrid.ReadOnly = true;

            datagrid.Columns[3].Visible = false;
            datagrid.Columns[0].Width = 200;
            datagrid.Columns[1].Width = 50;
            datagrid.Columns[2].Width = 120;
            //datagrid.Columns[5].Width = 80;
            // insert edit button into datagridview

            editButton = new DataGridViewButtonColumn();
            editButton.HeaderText = "Edit";
            editButton.Text = "Edit";
            editButton.UseColumnTextForButtonValue = true;
            editButton.Width = 80;
            datagrid.Columns.Add(editButton);
            // insert delete button to datagridview
            deleteButton = new DataGridViewButtonColumn();
            deleteButton.HeaderText = "Delete";
            deleteButton.Text = "Delete";
            deleteButton.UseColumnTextForButtonValue = true;
            deleteButton.Width = 80;
            datagrid.Columns.Add(deleteButton);


        }
        public void ShowCollectionData(System.Windows.Forms.DataGridView datagrid, string sqlQueryString)
        {
            OleDbCommand SQLQuery = new OleDbCommand();
            DataTable data = null;
            datagrid.DataSource = null;
            datagrid.DataSource = null;
            SQLQuery.Connection = null;
            OleDbDataAdapter dataAdapter = null;
            datagrid.Columns.Clear(); // <-- clear columns

            //---------------------------------
            SQLQuery.CommandText = sqlQueryString;
            SQLQuery.Connection = dbCtrl.database;
            data = new DataTable();
            dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
            datagrid.DataSource = data;
            datagrid.AllowUserToAddRows = false; // remove the null line
            datagrid.ReadOnly = true;

            datagrid.Columns[6].Visible = false;
            //datagrid.Columns[0].Width = 200;
            //datagrid.Columns[1].Width = 50;
            //datagrid.Columns[2].Width = 120;
            ////datagrid.Columns[5].Width = 80;
            //// insert edit button into datagridview

            editButton = new DataGridViewButtonColumn();
            editButton.HeaderText = "Edit";
            editButton.Text = "Edit";
            editButton.UseColumnTextForButtonValue = true;
            editButton.Width = 80;
            datagrid.Columns.Add(editButton);
            // insert delete button to datagridview
            deleteButton = new DataGridViewButtonColumn();
            deleteButton.HeaderText = "Delete";
            deleteButton.Text = "Delete";
            deleteButton.UseColumnTextForButtonValue = true;
            deleteButton.Width = 80;
            datagrid.Columns.Add(deleteButton);


        }

        private void txtFlatNo_TextChanged(object sender, EventArgs e)
        {

        }

        private void dataGridViewCollectionAll_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void btnAddMember_Click(object sender, EventArgs e)
        {

            if ((isValidflatNo(txtFlatNumber) == true) &&
                (isValidMemberName(txtMemberName) == true) &&
                (isValidShareNo(txtShareHoldingNumber) == true)
                )
            {
                string queryString = "INSERT INTO tblMemberList (  FlatNumber, Name,ShareHoldingNumber )VALUES('" +
                    txtFlatNumber.Text + "','" + txtMemberName.Text + "','" + txtShareHoldingNumber.Text + "')";
                int nResp = dbCtrl.executeQuery(queryString);
                if (nResp == 1)
                {
                    MessageBox.Show("Member added successfully");
                    txtFlatNumber.Clear();
                    txtMemberName.Clear();
                    txtShareHoldingNumber.Clear();
                    return;
                }
                else
                {
                    MessageBox.Show("Member can not be added ", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }


        private bool isEmpty(TextBox txtBox)
        {
            if (txtBox.TextLength == 0)
            {
                MessageBox.Show("Empty Field", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                txtBox.Select();
                return true;
            }
            return false;
        }

        private bool isValidShareNo(TextBox txtShareHoldingNumber)
        {
            if (isEmpty(txtShareHoldingNumber) == true)
            {
                return false;
            }

            return true;
        }

        private bool isValidMemberName(TextBox txtMemberName)
        {
            if (isEmpty(txtMemberName) == true)
            {
                return false;
            }

            return true;
        }

        private bool isValidflatNo(TextBox txtFlatNumber)
        {
            if (isEmpty(txtFlatNumber) == true)
            {
                return false;
            }

            return true;
        }

        private void btnMemberSearch_Click(object sender, EventArgs e)
        {
            if (isValidflatNo(txtFlatNoSearch) == true)
            {
                LoadMemberSearchResult(txtFlatNoSearch.Text);
            }
        }

        private void LoadMemberSearchResult(string strflatNo)
        {
            string queryString = "select * from tblMemberList where tblMemberList.FlatNumber LIKE '" + strflatNo + "'";
            ShowMemberData(dataGridMemberSearch, queryString);
        }

        private void btnMemberRefresh_Click(object sender, EventArgs e)
        {
            string strflatNo = "";
            txtFlatNoSearch.Text = strflatNo;
            MemberRefreshSearch(strflatNo);

        }
        public void MemberRefreshSearch(string strflatNo)
        {
            txtFlatNoSearch.Text = strflatNo;
            string queryString = "select * from tblMemberList where tblMemberList.FlatNumber LIKE '" + strflatNo + "'";
            ShowMemberData(dataGridMemberSearch, queryString);
        }

        private void dataGridMemberSearch_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int currentRow = int.Parse(e.RowIndex.ToString());
            string strDataBaseId = dataGridMemberSearch[3, currentRow].Value.ToString();

            if (dataGridMemberSearch.Columns[e.ColumnIndex] == editButton)
            {
                string strName = dataGridMemberSearch[0, currentRow].Value.ToString();
                string strFlat = dataGridMemberSearch[1, currentRow].Value.ToString();
                string strShareHoldingNo = dataGridMemberSearch[2, currentRow].Value.ToString();

                frmUpdateMember frmUpdate = new frmUpdateMember();

                frmUpdate.txtFlatNumber.Text = strFlat;
                frmUpdate.txtMemberName.Text = strName;
                frmUpdate.txtShareHoldingNumber.Text = strShareHoldingNo;
                frmUpdate.nDataBaseId = int.Parse(strDataBaseId);
                frmUpdate.Show();
                MemberRefreshSearch("");

            }
            if (dataGridMemberSearch.Columns[e.ColumnIndex] == deleteButton)
            {

                DialogResult nRes = MessageBox.Show("Are you sure to delete the selected record.Press Yes if you are sure.", "Warning", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
                if (nRes.Equals(DialogResult.Yes))
                {
                    int nDataBaseId = int.Parse(strDataBaseId);

                    string queryDeleteString = "DELETE FROM tblMemberList where  DatabaseId =" + nDataBaseId;
                    int response = dbCtrl.executeQuery(queryDeleteString);
                    if (response == 1)
                    {
                        MessageBox.Show("Deletetion successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        MemberRefreshSearch("");
                    }
                    else
                    {
                        MessageBox.Show("Deletetion Failed!", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }

                }

            }
        }

        private void btnCollectionRefresh_Click(object sender, EventArgs e)
        {

        }

        private void btnCollectionSearch_Click(object sender, EventArgs e)
        {
            string strFlatNo = "*";
            string strYear = "*";
            string strCollectiontype = "*";
            if (chkByFlatNumber.Checked && isValidflatNo(txtFlatNo) == true)
            {
                strFlatNo = txtFlatNo.Text;
            }
            if(chkByYear.Checked && isValidYear(txtCollectionYear) ==true)
             {
                 strYear = txtCollectionYear.Text;
             }
            if (chkByCollectionType.Checked )
            {
                strCollectiontype = cmbCollectionType.Text;
            } 
            LoadCollectionSearchResult(strFlatNo,strYear,strCollectiontype);
            
        }

        private bool isValidYear(TextBox txtCollectionYear)
        {
            if (isEmpty(txtCollectionYear) == true)
            {
                return false;
            }

            return true;
        }

        private void LoadCollectionSearchResult(string strFlatNo, string strYear, string strCollectiontype)
        {
            string queryString = "";
            int nParamacnt = 0;
            if (strFlatNo != "*")
            {
                nParamacnt++;
            }
            if (strYear != "*")
            {
                nParamacnt++;
            }
            if (strCollectiontype != "*")
            {
                nParamacnt++;
            }
            queryString = "select * from tblCollection  ";
            if (nParamacnt == 1)
            {
                
                if (strFlatNo != "*")
                {
                    queryString += " where tblCollection.FlatNumber LIKE '" + strFlatNo + "'";
                }
                else if (strYear != "*")
                {
                    queryString += " where tblCollection.Year LIKE '" + strYear + "'";
                }
                else if (strCollectiontype != "*")
                {
                    queryString += " where tblCollection.CollectionType LIKE '" + strCollectiontype + "'";
                }
            }
            else if (nParamacnt == 2)
            {
                if (strFlatNo != "*")
                {
                    queryString += " where tblCollection.FlatNumber LIKE '" + strFlatNo + "'";
                    if (strYear != "*")
                    {
                        queryString += " AND tblCollection.Year LIKE '" + strYear + "'";
                    }
                    else if (strCollectiontype != "*")
                    {
                        queryString += " AND tblCollection.CollectionType LIKE '" + strCollectiontype + "'";
                    }
                }
                else if (strYear != "*")
                {
                    queryString += " where tblCollection.Year LIKE'" + strYear + "'";
                    if (strCollectiontype != "*")
                    {
                        queryString += " AND tblCollection.CollectionType LIKE '" + strCollectiontype + "'";
                    }
                }                
            }
            else if (nParamacnt == 3)
            {
                queryString = "select * from tblCollection where FlatNumber LIKE '" + strFlatNo + "' AND Year LIKE '" +
                      strYear + "' AND CollectionType LIKE '" + strCollectiontype + "'";
            }
            ShowCollectionData(dataGridViewSearchResult, queryString);
        }

        private void btnCollectionAdd_Click(object sender, EventArgs e)
        {

        }

        private void chkByFlatNumber_CheckedChanged(object sender, EventArgs e)
        {
            txtFlatNo.Enabled = chkByFlatNumber.Checked;
        }

        private void chkByYear_CheckedChanged(object sender, EventArgs e)
        {
            txtCollectionYear.Enabled = chkByYear.Checked;
        }

        private void chkByCollectionType_CheckedChanged(object sender, EventArgs e)
        {
            cmbCollectionType.Enabled = chkByCollectionType.Checked;

        }

    }
}